package com.example.crm.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.crm.entity.Contract;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 合同Mapper接口
 */
@Mapper
public interface ContractMapper extends BaseMapper<Contract> {
    
    /**
     * 根据客户ID查询合同列表
     */
    @Select("SELECT c.*, cu.name as customer_name, o.name as opportunity_name FROM crm_contract c " +
            "LEFT JOIN crm_customer cu ON c.customer_id = cu.id " +
            "LEFT JOIN crm_opportunity o ON c.opportunity_id = o.id " +
            "WHERE c.customer_id = #{customerId} AND c.deleted = 0")
    List<Contract> selectByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 统计各状态的合同数量
     */
    @Select("SELECT status, COUNT(*) as count FROM crm_contract WHERE deleted = 0 GROUP BY status")
    List<Map<String, Object>> countGroupByStatus();
    
    /**
     * 统计近6个月的合同金额
     */
    @Select("SELECT DATE_FORMAT(sign_date, '%Y-%m') as month, SUM(amount) as amount " +
            "FROM crm_contract " +
            "WHERE deleted = 0 AND sign_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) " +
            "GROUP BY DATE_FORMAT(sign_date, '%Y-%m') " +
            "ORDER BY month")
    List<Map<String, Object>> monthlySalesLast6Months();
    
    /**
     * 统计本月新签合同数量
     */
    @Select("SELECT COUNT(*) FROM crm_contract WHERE deleted = 0 AND DATE_FORMAT(sign_date, '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')")
    Long countNewContractsThisMonth();
    
    /**
     * 统计本月新签合同金额
     */
    @Select("SELECT IFNULL(SUM(amount), 0) FROM crm_contract WHERE deleted = 0 AND DATE_FORMAT(sign_date, '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')")
    BigDecimal sumNewContractAmountThisMonth();
} 